import sys, os
if hasattr(sys, 'frozen'):
    os.environ['PATH'] = sys._MEIPASS + ";" + os.environ['PATH']
import os
import re
import pymysql
import sqlite3
from inv_config import *
from bot_log import BotLogger
logger = BotLogger(os.path.basename(__file__)).getLogger()

customer_code_table_create_sql = '''
CREATE TABLE IF NOT EXISTS customer_code (
compay_name VARCHAR(50),
customer_code VARCHAR(50),
tax_num VARCHAR(50),
id_num VARCHAR(50),
address VARCHAR(50),
account_bank VARCHAR(50),
email VARCHAR(50),
status VARCHAR(50),
customer_id VARCHAR(50)
)
'''

commodity_code_table_create_sql = '''
CREATE TABLE IF NOT EXISTS commodity_code (
commodity_name VARCHAR(50),
commodity_code VARCHAR(50),
model VARCHAR(50),
unit VARCHAR(50),
price VARCHAR(50),
status VARCHAR(50),
commodity_id VARCHAR(50)
)
'''

sum_upload_table_create_sql = '''
CREATE TABLE IF NOT EXISTS sum_upload (
upload_sum VARCHAR(10),
reverse_writing_monitor VARCHAR(10),
ukey_num VARCHAR(50)

)
'''

def get_conn():
    conn = sqlite3.connect(get_cfg().db_path)
    if conn is None:
        conn = sqlite3.connect(':memory:')

    cur = conn.cursor()
    return conn, cur

def close_all(conn, cur):
    # logger.info("To close the connection and cursor")
    if cur is not None:
        cur.close()

    if conn is not None:
        conn.close()

def init_db():
    try:
        conn, cur = get_conn()
        if conn is None or cur is None:
            return False

        cur.execute(customer_code_table_create_sql)
        cur.execute(commodity_code_table_create_sql)
        cur.execute(sum_upload_table_create_sql)
        # # create the refaccount table
        # cur.execute(account_table_create_sql)
        # # create the tax op type table
        # cur.execute(bot_op_type_create_sql)

        # cur.execute("SELECT * FROM op_type")
        # op_types = cur.fetchall()
        # if not op_types:
        #     cur.execute(delete_op_type_sql)
        #     cur.execute("INSERT INTO op_type VALUES(1, 1, 1, 1, 1, 1, 1)")
        conn.commit()
        close_all(conn, cur)
        return True
    except Exception as e:
        logger.error(str(e))

    return False